﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    private SqlConnection conn;

    public SqlConnection Conn
    {
        get { return conn; }
        set { conn = value; }
    }
    private SqlDataAdapter adapter;
	public DataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public SqlConnection OpenConnection()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConectionString"].ConnectionString);
        try
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
            }
            else
            {
                conn.Open();
            }

        }
        catch
        {
            return null;
        }
        return conn;
    }
    /// <summary>
    /// close a connection
    /// </summary>
    /// <param Name="sqlCn"></param>
    public void CloseConnection(SqlConnection sqlCn)
    {
        if (sqlCn != null)
        {
            if (sqlCn.State == ConnectionState.Open)
                sqlCn.Close();
            sqlCn.Dispose();
        }
    }

    /// <summary>
    /// Execute all store procedure without parammeter
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <returns>Sql data in a DataTable</returns>
    public DataTable executeSelect(string spName)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnection();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

        adapter.Fill(dataSet);

        //Close connection
        CloseConnection(conn);
        return dataSet.Tables[0];


    }

    /// <summary>
    /// Execute all store procedure has parammeter
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <param Name="paramList">Parameters of the store procedure</param>
    /// <returns>Sql data in a DataTable</returns>
    public DataTable executeSelect(string spName, SqlParameter[] paramList)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnection();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        adapter.SelectCommand.Parameters.AddRange(paramList);

        adapter.Fill(dataSet);
        //Close connection
        CloseConnection(conn);

        return dataSet.Tables[0];
    }
    

    /// <summary>
    /// Execute all store procedure performs insert, update, delete
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <param Name="paramList">Parameters of the store procedure</param>
    /// <returns>Sql data in a DataTable</returns>
    public SqlParameter[] initialization(int length_parameter)
    {
        SqlParameter[] parameter = new SqlParameter[length_parameter];
        for (int i = 0; i < length_parameter; i++)
        {
            parameter[i] = AddParameter("Null"+i, DBNull.Value, SqlDbType.Int);
        }
        return parameter;
    }
    public int executeUpdate(string spName, SqlParameter[] paramList)
    {
        int result = 0;
        //Open connection
        conn = OpenConnection();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paramList);

        try
        {
            result = command.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            //relationship error
            result = 0;
        }
        //Close connection
        CloseConnection(conn);

        return result;
    }

    /// <summary>
    /// Execute store procedure get result executed.
    /// </summary>
    /// <param Name="spName"></param>
    /// <param Name="paramList"></param>
    /// <returns></returns>
    public int executeScalary(string spName, SqlParameter[] paramList)
    {
        int result = 0;
        //Open connection
        conn = OpenConnection();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paramList);

        result = int.Parse(command.ExecuteScalar().ToString());
        //Close connection
        CloseConnection(conn);

        return result;

    }
    public SqlParameter AddParameter(string parameterName, object parameterValue, SqlDbType parameterSqlType,object size=null)
    {
        SqlParameter parameter;
        if (!parameterName.StartsWith("@"))
        {
            parameterName = "@" + parameterName;
        }
        if(size!=null){
            parameter=new SqlParameter(parameterName,parameterSqlType,Convert.ToInt32(size));
        }else{
            parameter=new SqlParameter(parameterName,parameterSqlType);
        }
        parameter.Value=parameterValue;
        return parameter;
        
    }
}